﻿-- =============================================
-- Author:		Taylor Chase
-- Create date: April 28 2016
-- Description:	Takes a generic or specific chain of relationships and entities and verifies the security continuity 
--				of the items in the chain.
-- =============================================
CREATE PROCEDURE [dbo].[VerifyRequestChain] 
	@RequestorEntityID nvarchar(100),
	@RequestedChain nvarchar(Max),
	@ApprovedChain nvarchar(MAX) = NULL,
	@ApprovedRD int = NULL,
	@ApprovedRC int = NULL,
	@ApprovedWD int = NULL,
	@ApprovedWC int = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @E1ID nvarchar(100)
	DECLARE @SkipStep bit
	DECLARE @RelNameOrID nvarchar(100)
	DECLARE @ErrorMessage nvarchar(MAX)
	DECLARE @OldRelName nvarchar(100)
	DECLARE @OldID nvarchar(100)
	DECLARE @EntityType nvarchar(100)
	DECLARE @RelationshipName nvarchar(12),@RelationshipDirection nvarchar(12)
	DECLARE @NOSQL nvarchar(MAX), @CountRows int

	IF @RequestedChain IS NULL OR @RequestedChain = ''
		SET @RequestedChain = ';'
	IF @ApprovedChain IS NULL OR @ApprovedChain = ''
		SET @ApprovedChain = ';'
	-- Check to make sure approved security is paired to requested chain
	-- if it isn't, clear the permissions.
	IF @RequestedChain = ';' BEGIN
		SET @ApprovedRC = NULL
		SET @ApprovedRD = NULL
		SET @ApprovedWC = NULL
		SET @ApprovedWD = NULL
	END

	IF LEFT(@RequestedChain,1) <> ';' OR LEFT(@ApprovedChain,1) <> ';' OR 
	   RIGHT(@RequestedChain,1) <> ';' OR RIGHT(@ApprovedChain,1) <> ';' BEGIN
	   -- Force the system to drop the loop and return an error if it does not begin and end with ;
	   SET @ErrorMessage = 'Error: Either the ApprovedChain or the RequestedChain do not begin or end with a ";".  '
						 + 'Both chains follow for reference: RequestedChain "' + @RequestedChain + '" '
						 + 'ApprovedChain "' + @ApprovedChain + '"'
	   SET @RequestedChain = ';'
	END


	-- Sample Chain: 10214 - Then ;FATHER.Forward;10450;ENROLLED_C.Forward;LANG10FR2_15_16_1;
	SET @E1ID = @RequestorEntityID
	WHILE @RequestedChain <> ';' BEGIN
		-- Until the entire chain has been processed, continue the loop.
		SET @CountRows = 0
		SET @RelationshipName = NULL
		SET @RelationshipDirection = NULL
		SET @SkipStep = 0
		-- Take the beginning semicolon off
		SET @RequestedChain = SUBSTRING(@RequestedChain,2,LEN(@RequestedChain))
		-- Assign next chain so far item to @RelnameOrID
		SET @RelNameOrID = SUBSTRING(@RequestedChain,1,CHARINDEX(';',@RequestedChain)-1)
		SET @RequestedChain = SUBSTRING(@RequestedChain,CHARINDEX(';',@RequestedChain),LEN(@RequestedChain))

		-- Determine if the item is an ID or a Relationship
		IF CHARINDEX('.',@RelNameOrID) <> 0 BEGIN
			SET @RelationshipName = SUBSTRING(@RelNameOrID,1,CHARINDEX('.',@RelNameOrID)-1)
			SET @RelationshipDirection = SUBSTRING(@RelNameOrID,CHARINDEX('.',@RelNameOrID)+1,LEN(@RelNameOrID))
		END ELSE BEGIN
			SET @RelationshipName = @RelNameOrID
			SET @RelationshipDirection = NULL
		END

		-- Ensure this is a relationship.
		SELECT @RelationshipName = ShortRelationshipName
		FROM DefinedEntityRelationships WHERE (@RelationshipDirection = 'Forward' OR @RelationshipDirection = 'Reverse'
			OR @RelationshipDirection IS NULL)
			AND @RelationshipName = ShortRelationshipName
		SET @CountRows = @@ROWCOUNT

		IF @CountRows = 1 BEGIN
			-- It is a relationship
			IF @RequestedChain <> ';' OR @OldRelName IS NULL
				SET @OldRelName = @RelNameOrID
		END ELSE IF NOT @OldRelName IS NULL BEGIN
			-- It is an ID, but if it is not preceeded by a relationship name, quit immediately (see final else)
			SET @OldID = @RelNameOrID
			IF CHARINDEX('.',@OldRelName) <> 0 BEGIN
				SET @RelationshipName = SUBSTRING(@OldRelName,1,CHARINDEX('.',@OldRelName)-1)
				SET @RelationshipDirection = SUBSTRING(@OldRelName,CHARINDEX('.',@OldRelName)+1,LEN(@OldRelName))
			END ELSE
				SET @RelationshipName = @OldRelName

			SELECT @EntityType = EntityType 
			FROM (
				SELECT Entity1Type AS EntityType
				FROM DefinedEntityRelationships WHERE ShortRelationshipName = @RelationshipName
				AND @RelationshipDirection = 'Forward'
				UNION
				SELECT Entity2Type AS EntityType
				FROM DefinedEntityRelationships WHERE NOT ReverseRelationship IS NULL AND
				ShortRelationshipName = @RelationshipName AND @RelationshipDirection = 'Reverse' 
				UNION
				SELECT Entity1Type AS EntityType
				FROM DefinedEntityRelationships WHERE NOT ReverseRelationship IS NULL AND
				ShortRelationshipName = @RelationshipName AND @RelationshipDirection IS NULL 
			) AS RelTable

		END ELSE BEGIN
			SET @SkipStep = 1
			SET @RequestedChain = ';'
			SET @ErrorMessage = 'Error: The item named ' + COALESCE(@OldRelName,@OldID,'NOT NAMED') + ' is not a valid relationship.'
		END

		--PRINT 'RelNameOrID ' + @RelNameOrID
		--PRINT 'OldRelName ' + COALESCE(@OldRelName,'')
		--PRINT 'OldID ' + COALESCE(@OldID,'')
		--PRINT 'EntityType ' + COALESCE(@EntityType,'')
		IF @ApprovedChain <> ';' BEGIN
			-- Take off the beginning semicolon
			SET @ApprovedChain = SUBSTRING(@ApprovedChain,2,LEN(@ApprovedChain))
			-- IF the Relationship/ID name matches "approved chain so far", then skip to the next item.
			-- IF NOT, quit.
			IF @RelNameOrID = SUBSTRING(@ApprovedChain,1,CHARINDEX(';',@ApprovedChain)-1) BEGIN
				SET @SkipStep = 1
			END ELSE BEGIN
				-- Skip the rest of this process, kill the while loop, and set error message.
				SET @SkipStep = 1
				SET @RequestedChain = ';'
				SET @ErrorMessage = 'Error: Approved Chain and Requested Chain do not match at ' + COALESCE(@RelNameOrID,'') + ' and ' + SUBSTRING(@ApprovedChain,1,CHARINDEX(';',@ApprovedChain)-1)
			END
			-- Remove the first item in the chain and be ready for the next step.
			SET @ApprovedChain = SUBSTRING(@ApprovedChain,CHARINDEX(';',@ApprovedChain),LEN(@ApprovedChain))
		END

		-- Only enter if the item is an ID, otherwise skip.
		IF @SkipStep <> 1 AND @OldID = @RelNameOrID BEGIN
			-- If the Approved permission values are null, they will be set by this command
			-- otherwise they will be updated to the next relationship level.
			SELECT @ApprovedRD = E1_Read_Direct_E2,@ApprovedRC = E1_Read_Child_E2,
			@ApprovedWD = E1_Write_Direct_E2, @ApprovedWC = E1_Write_Child_E2
			FROM dbo.GetEntityRelationships(@EntityType,@ApprovedRC,@ApprovedWC)
			WHERE NOT @OldRelName IS NULL AND (SRNameWithDir = @OldRelName 
				OR SRNameWithDir = @OldRelName + '.Forward'
				OR SRNameWithDir = @OldRelName + '.Reverse')
			--SET @CountRows = @@ROWCOUNT

			IF @@ROWCOUNT = 0 BEGIN
				-- Skip the rest of this process, kill the while loop, and set error message.
				SET @SkipStep = 1
				SET @RequestedChain = ';'
				SET @ErrorMessage = 'Error: The relationship named ' + COALESCE(@OldRelName,@OldID,'NOT NAMED') + ' does not allow access, is missing a "forward" or "reverse" or does not exist.'
			END ELSE BEGIN
				-- If Rowcount is not zero, the relationship allows access.
				-- Now the OldID needs to be located in the list of entities to make sure it actually exists.
				SET @NOSQL = 'SELECT Entity2ID FROM [{' + @OldRelName + '} WHERE Entity1ID = "' + @E1ID 
									+ '" AND Entity2ID = "' + @OldID + '"]'
									PRINT @NOSQL
				SET @CountRows = 0
				-- This checks the actual relationship table to determine if the ID is present.
				SET @NOSQL = dbo.convertrafikinosql(@NOSQL,default,default,default,default);
				SET @NOSQL = 'SELECT @CountRows=COUNT(*) FROM (' + @NOSQL + ') AS B'
				EXECUTE sp_executesql  
					@stmt = @NOSQL,
					@params = N'@CountRows int OUTPUT',
					@CountRows = @CountRows OUTPUT

				IF @CountRows = 0 BEGIN
					-- Skip the rest of this process, kill the while loop, and set error message.
					SET @SkipStep = 1
					SET @RequestedChain = ';'
					SET @ErrorMessage = 'Error: The entity with an id of ' + COALESCE(@OldID,'') + ' is not in a relationship named ' + COALESCE(@OldRelName,'')
										+ ' with an entity with an id of ' + COALESCE(@E1ID,'')
				END
			END
		END
		-- If only a relationship is passed, make sure to maintain the E1ID
		SET @E1ID = COALESCE(@OldID,@E1ID)
	END

	IF @ErrorMessage IS NULL BEGIN
		-- Find the entity type:
		IF @E1ID = @RequestorEntityID BEGIN
			-- This is the first level (just a ;), so pass back all 2s for permissions and 
			-- an entitytype of Person
			SET @EntityType = 'Person'
			SET @ApprovedRC = 2
			SET @ApprovedRD = 2
			SET @ApprovedWC = 2
			SET @ApprovedWD = 2
		END ELSE BEGIN
			IF CHARINDEX('.',@OldRelName) <> 0 BEGIN
				SET @RelationshipName = SUBSTRING(@OldRelName,1,CHARINDEX('.',@OldRelName)-1)
				SET @RelationshipDirection = SUBSTRING(@OldRelName,CHARINDEX('.',@OldRelName)+1,LEN(@OldRelName))
			END ELSE BEGIN
				SET @RelationshipName = @OldRelName
				SET @RelationshipDirection = NULL
			END

			SELECT @EntityType = EntityType 
			FROM (
				SELECT Entity2Type AS EntityType
				FROM DefinedEntityRelationships WHERE ShortRelationshipName = @RelationshipName
				AND @RelationshipDirection = 'Forward'
				UNION
				SELECT Entity1Type AS EntityType
				FROM DefinedEntityRelationships WHERE NOT ReverseRelationship IS NULL AND
				ShortRelationshipName = @RelationshipName AND @RelationshipDirection = 'Reverse' 
				UNION
				SELECT Entity2Type AS EntityType
				FROM DefinedEntityRelationships WHERE NOT ReverseRelationship IS NULL AND
				ShortRelationshipName = @RelationshipName AND @RelationshipDirection IS NULL 
			) AS RelTable
		END

		-- Find the approved security values.
		SELECT @ApprovedRD = E1_Read_Direct_E2,@ApprovedRC = E1_Read_Child_E2,
		@ApprovedWD = E1_Write_Direct_E2, @ApprovedWC = E1_Write_Child_E2
		FROM dbo.GetEntityRelationships(@EntityType,@ApprovedRC,@ApprovedWC)
		WHERE SRNameWithDir = @OldRelName

		-- If RelNameOrID is different from OldID, then it is an ending relationship that should be used for filtering.
		SELECT @E1ID AS EntityID,@EntityType AS EntityType,IIF(@RelNameOrID <> @E1ID,@RelNameOrID,NULL) AS EndingRelationship,
			@ApprovedRD AS RD,@ApprovedRC AS RC,@ApprovedWD AS WD,@ApprovedWC AS WC
		--PRINT 'Finished Chain'
		--PRINT @OldID
		--PRINT @OldRelName
		--PRINT 'RD ' + CAST(@ApprovedRD AS NVARCHAR)
		--PRINT 'RC ' + CAST(@ApprovedRC AS NVARCHAR)
		--PRINT 'WC ' + CAST(@ApprovedWC AS NVARCHAR)
		--PRINT 'WD ' + CAST(@ApprovedWD AS NVARCHAR)
	END ELSE BEGIN
		SELECT @ErrorMessage AS EntityID,NULL AS EntityType,NULL AS EndingRelationship,NULL AS RD,NULL AS RC,NULL AS WD,NULL AS WC
	END



	-- Returns EndingEntityID,EndingEntityType,EndingRelationship (or null),4 Associated permissions to EndingEntity
	RETURN 0
END